﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;
using System.Data.SqlClient;

namespace DataAccessService
{
  public class DataHelper : IDataHelper
  {
    static int chosenYear = 0;
    SqlCommand command = null;
    SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=CausesOfDeath;Integrated Security=True");
    SqlDataReader reader = null;
    
    public List<Causes> GetTopCauses(string year, string country)
    {
      List<Causes> topCauses = new List<Causes>();
      connection.Open();
      string query = "select distinct(CauseOfDeath) as Cause, SUM(Value) as Deaths from CausesOfDeath where "
        + "Year = '{0}' and Country = '{1}' and CauseOfDeath NOT LIKE 'External causes%' and CauseOfDeath NOT LIKE 'All causes%'  "
        + "group by CauseOfDeath order by Deaths DESC";
      command = new SqlCommand(String.Format(query, year, country), connection);
      reader = command.ExecuteReader();
      if (reader != null)
      {
        int count = 0;
        while (reader.Read() && count < 5)
        {
          Causes cause = new Causes();
          cause.Cause = reader["Cause"].ToString().Trim();
          cause.Count = Int32.Parse(reader["Deaths"].ToString());
          topCauses.Add(cause);
          count++;
        }
      }
      connection.Close();
      return topCauses;
    }

    public List<Int32> GetTopCausesMaleStats(string year, string country, List<Causes> causes)
    {
      List<Int32> breakUp = new List<int>();
      connection.Open();
      string query = "select distinct(CauseOfDeath) as Cause, SUM(Value) as Deaths from CausesOfDeath where Year = '{0}' and Sex = 'Male' and Country = '{1}' and CauseOfDeath IN ('{2}', '{3}', '{4}', '{5}', '{6}') group by CauseOfDeath order by Deaths DESC";
      command = new SqlCommand(String.Format(query, year, country, causes[0].Cause.Trim(),
        causes[1].Cause.Trim(),
        causes[2].Cause.Trim(),
        causes[3].Cause.Trim(),
        causes[4].Cause.Trim()), connection);
      reader = command.ExecuteReader();
      if (reader != null)
      {
        while (reader.Read())
        {
          breakUp.Add(Int32.Parse(reader["Deaths"].ToString()));
        }
      }
      connection.Close();
      return breakUp;
    }

    public List<Int32> GetTopCausesFemaleStats(string year, string country, List<Causes> causes)
    {
      List<Int32> breakUp = new List<int>();
      connection.Open();
      string query = "select distinct(CauseOfDeath) as Cause, SUM(Value) as Deaths from CausesOfDeath where Year = '{0}' and Sex = 'Female' and Country = '{1}' and CauseOfDeath IN ('{2}', '{3}', '{4}', '{5}', '{6}') group by CauseOfDeath order by Deaths DESC";
      command = new SqlCommand(String.Format(query, year, country, causes[0].Cause.Trim(),
        causes[1].Cause.Trim(),
        causes[2].Cause.Trim(),
        causes[3].Cause.Trim(),
        causes[4].Cause.Trim()), connection);
      reader = command.ExecuteReader();
      if (reader != null)
      {
        while (reader.Read())
        {
          breakUp.Add(Int32.Parse(reader["Deaths"].ToString()));
        }
      }
      connection.Close();
      return breakUp;
    }

    public int GetMaxValue(string country)
    {
      int maxCount = 0;
      connection.Open();
      string query = "select distinct(CauseOfDeath) as Cause, SUM(Value) as Deaths from CausesOfDeath where "
        + "Country = '{0}' and CauseOfDeath NOT LIKE 'External causes%' and CauseOfDeath NOT LIKE 'All causes%' "
        + "group by CauseOfDeath, Year order by Deaths DESC";
      command = new SqlCommand(String.Format(query, country), connection);
      reader = command.ExecuteReader();
      if (reader != null)
      {
        int count = 0;
        reader.Read();
        maxCount = Int32.Parse(reader["Deaths"].ToString());
        count++;
      }
      connection.Close();
      return maxCount;
    }

    public Int32 GetMvFMaxValue(string country)
    {
      int maxCount = 0;
      connection.Open();
      string query = "select distinct(CauseOfDeath) as Cause, SUM(Value) as Deaths from CausesOfDeath where Country = '{0}' and CauseOfDeath NOT LIKE 'External causes%' and CauseOfDeath NOT LIKE 'All causes%' group by CauseOfDeath, Year, Sex order by Deaths DESC";
      command = new SqlCommand(String.Format(query, country), connection);
      reader = command.ExecuteReader();
      if (reader != null)
      {
        int count = 0;
        reader.Read();
        maxCount = Int32.Parse(reader["Deaths"].ToString());
        count++;
      }
      connection.Close();
      return maxCount;
    }

    public List<int> GetDataPoints(string country)
    {
      List<int> years = new List<int>();
      connection.Open();
      string query = "select distinct(Year) as year from CausesOfDeath where Country = '{0}' order by year";
      command = new SqlCommand(String.Format(query, country), connection);
      reader = command.ExecuteReader();
      if (reader != null)
      {
        while (reader.Read())
        {
          years.Add(int.Parse(reader["year"].ToString()));
        }
      }
      connection.Close();
      return years;
    }

    public List<CauseProgression> GetCauseProgression(string country, string cause)
    {
      List<CauseProgression> progression = new List<CauseProgression>();
      connection.Open();
      string query = "select distinct(CausesOfDeath.Year) as year, SUM(Value) as Deaths from CausesOfDeath where Country = '{0}' and CauseOfDeath LIKE '{1}%' group by Year order by Year";
      command = new SqlCommand(String.Format(query, country, cause), connection);
      reader = command.ExecuteReader();
      if (reader != null)
      {
        while (reader.Read())
        {
          CauseProgression cp = new CauseProgression();
          cp.Year = reader["year"].ToString();
          cp.Count = Int32.Parse(reader["Deaths"].ToString());
          progression.Add(cp);
        }
      }
      connection.Close();
      return progression;
    }

    public int GetMaxValueForCause(string country, string cause)
    {
      int maxCount = 0;
      connection.Open();
      string query = "select distinct(CausesOfDeath.Year) as year, SUM(Value) as Deaths from CausesOfDeath where Country = '{0}' and CauseOfDeath LIKE '{1}%' group by Year order by Deaths DESC";
      command = new SqlCommand(String.Format(query, country, cause), connection);
      reader = command.ExecuteReader();
      if (reader != null)
      {
        reader.Read();
        maxCount = Int32.Parse(reader["Deaths"].ToString());
      }
      connection.Close();
      return maxCount;
    }

    public Int32 GetAllCausesForCountry(string country, string year)
    {
      Int32 total = 0;
      connection.Open();
      string query = "select SUM(Value) as Deaths from CausesOfDeath where Country = '{0}' and Year = '{1}' and CauseOfDeath LIKE 'All causes%'";
      command = new SqlCommand(String.Format(query, country, year), connection);
      reader = command.ExecuteReader();
      if (reader != null)
      {
        reader.Read();
        if (reader["Deaths"] == DBNull.Value)
          total = -1;
        else
          total = Int32.Parse(reader["Deaths"].ToString());
      }
      connection.Close();
      return total;
    }

    public double GetCountsForCountry(string country, string cause, string year)
    {
      Int32 totalDeaths = 0;
      connection.Open();
      string query = "select SUM(Value) as Deaths from CausesOfDeath where CauseOfDeath LIKE '{0}%' and Country = '{1}' and Year = '{2}'";
      command = new SqlCommand(String.Format(query, cause, country, year), connection);
      reader = command.ExecuteReader();
      if (reader != null)
      {
        reader.Read();
        totalDeaths = Int32.Parse(reader["Deaths"].ToString());
      }
      connection.Close();
      Int32 allDeaths = 0;
      double percentage = 0;
      allDeaths = GetAllCausesForCountry(country, year);
      if (allDeaths == -1)
        percentage = 0;
      else
      {
        percentage = (double)totalDeaths / (double)allDeaths;
      }
      return percentage;
    }
      
    
    public List<CountPerCountry> GetCountsPerCountry(string cause, string year, string country)
    {
      List<CountPerCountry> listCPC = new List<CountPerCountry>();
      connection.Open();
      string query = "select distinct(Country) as Country, SUM(Value) as Deaths from CausesOfDeath where CauseOfDeath LIKE '{0}%' and Year = '{1}' and Country <> '{2}' group by Country order by Deaths DESC";
      command = new SqlCommand(String.Format(query, cause, year, country), connection);
      reader = command.ExecuteReader();
      if (reader != null)
      {
        while (reader.Read())
        {
          CountPerCountry cpc = new CountPerCountry();
          cpc.Country = reader["Country"].ToString().Trim();
          cpc.Count = reader["Deaths"].ToString().Trim();
          listCPC.Add(cpc);
        }
      }
      connection.Close();
      Int32 total = 0;
      double percentage = 0;
      foreach (CountPerCountry cpc in listCPC)
      {
        total = GetAllCausesForCountry(cpc.Country, year);
        if (total == -1)
          cpc.Count = 0.ToString();
        else
        {
          percentage = Double.Parse(cpc.Count) / total;
          cpc.Count = percentage.ToString();
        }
      }
      return listCPC;
    }
  }
}
